/**
 *Review数据模型模块
 */

// 导入 MySQL 模块
const util = require("../../common/util");
// 创建 MySQL 连接池
const pool = require("../../common/util");

class ReviewDB {
    /**
     * @constructor
     * @private   
     */
    constructor() {
        this.instance = null;
        this.pool = pool;
    }

    /**
     * 创建 ReviewDB 对象
     * @returns {WarehouseDB} ReviewDB 实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new ReviewDB();
        }
        return this.instance;
    }
}
/**
 * 根据 id 查询 Review
 * @param {Number} id Review id
 * @returns {Promise<ReviewDB>} Review 对象
 */
ReviewDB.prototype.find = async function (id, userId) {
    try {
        let sql = `
                        SELECT 
                            a.id AS id,
                            a.user_id AS user_id,
                            c.username AS username,
                            a.content AS content,
                            a.message_time AS message_time,
                            a.reply_content AS reply_content,
                            a.reply_time AS reply_time                           
                        FROM reviews AS a 
                        LEFT JOIN users AS c
                        ON a.user_id = c.id
                        WHERE a.id = ? AND c.id = ?;
                    `;
        let params = [id, userId];
        const results = await this.pool.query(sql, params);
        return results;
    } catch (err) {
        throw err;
    }
};

/**
 * 获取评论列表
 * @param {number} userId 用户ID
 * @param {string} orderBy 排序字段，默认为 "id"
 * @param {string} sort 排序方式，"asc" 或 "desc"，默认为 "desc"
 * @param {number} limit 返回的记录数限制，-1 表示无限制
 * @param {number} offset 偏移量，-1 表示不使用偏移量
 * @returns {Promise<Array<Object>>} 评论列表
 */
ReviewDB.prototype.findAll = async function (orderBy = "id", sort = "desc", limit = -1, offset = -1) {
    try {
        let sql = `  
            SELECT   
                a.id AS id,  
                a.user_id AS user_id,  
                c.username AS username,  
                a.content AS content,  
                a.message_time AS message_time,  
                a.reply_content AS reply_content,  
                a.reply_time AS reply_time                             
            FROM reviews AS a   
            LEFT JOIN users AS c  
            ON a.user_id = c.id  
             
            ORDER BY ` + orderBy + ` ` + sort + `    
        `;
        let params = [];
        if (limit !== -1) {
            console.log(`limit2: ${limit}`)  
            sql += ` LIMIT ${limit}`;
            if (offset !== -1) {
                sql += ` OFFSET ${offset}`;
            }
        }
        const results = await this.pool.query(sql, params);
        return results;
    } catch (err) {
        console.error('Error fetching reviews:', err);
        throw err;
    }
};

/**
 * 新增评论
 * @param {Object} review 评论数据
 * @returns {Promise}
 */
ReviewDB.prototype.add = async function (review) {
    try {
        let sql = `INSERT INTO reviews(
                        user_id, content, message_time) 
                        VALUES(
                        ?, ?, ? );`;
        let params = [
            review.userId,
            review.content,
            review.messageTime
        ];
        const result = await this.pool.query(sql, params);
        return result.insertId;
    } catch (err) {
        throw err;
    }
};

/**
 * 修改
 * @param {Object} review 评论数据
 * @returns {Promise}
 */
ReviewDB.prototype.update = async function (review) {
    try {
        let sql = `UPDATE reviews SET
                        reply_content = ?, reply_time = ?,status = ?
                        WHERE id = ?;`;

        let params = [
            review.replyContent,
            review.replyTime,
            review.status,
            review.id
        ];

        const results = await this.pool.query(sql, params);
        return results.affectedRows;
    } catch (err) {
        throw err;
    }
};

/**
 * 删除指定评论
 * @param {Number} reviewId 评论ID
 * @returns {Promise}
 */
ReviewDB.prototype.remove = async function (reviewId) {
    try {
        let sql = "DELETE FROM reviews WHERE id = ?";
        let params = [reviewId];
        const result = await this.pool.query(sql, params);
        return result.affectedRows;
    } catch (err) {
        throw err
    }
};


/**
* 获取评论总数
* @param {Number} review 评论数据
* @returns {Promise}
*/
ReviewDB.prototype.getCount = async function () {
    try {
        let sql = "SELECT count(1) AS total FROM reviews";
        let params = [];
        const result = await this.pool.query(sql, params);
        return result;
    } catch (err) {
        throw err;
    }
};



/**
* 获取指定用户的评论信息列表
* @param {String} userId 用户ID
* @param {String} orderBy 排序字段
* @param {String} sort 排序方式
* @param {Number} limit 数量
* @param {Number} offset 开始
* @returns {Promise}
*/
ReviewDB.prototype.findAllByUserId = async function (userId, orderBy = "id", sort = "desc", limit = -1, offset = -1) {
    try {
        let sql = `  
            SELECT   
                a.id AS id,  
                a.user_id AS user_id,  
                c.username AS username,  
                a.content AS content,  
                a.message_time AS message_time,  
                a.reply_content AS reply_content,  
                a.reply_time AS reply_time                             
            FROM reviews AS a   
            LEFT JOIN users AS c  
            ON a.user_id = c.id  
            WHERE a.user_id = ?
            ORDER BY ` + orderBy + ` ` + sort + `    
        `;
        let params = [userId];
        if (limit !== -1) {
            console.log(`limit2: ${limit}`)  
            sql += ` LIMIT ${limit}`;
            if (offset !== -1) {
                sql += ` OFFSET ${offset}`;
            }
        }
        const results = await this.pool.query(sql, params);
        return results;
    } catch (err) {
        console.error('Error fetching reviews:', err);
        throw err;
    }
};

/**
* 获取指定用户的评论总数
* @param {Number} userId
* @returns {Promise}
*/
ReviewDB.prototype.getCountByUserId = async function (userId) {
    try {
        let sql = "SELECT count(1) AS total FROM reviews WHERE user_id = ?";
        let params = [userId];
        const result = await this.pool.query(sql, params);
        return result;
    } catch (err) {
        throw err;
    }
}

module.exports = ReviewDB;